package com.css.bjdt_core.reportDataContrast.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_core.reportDataContrast.entity.ReportExamineSumDzzId;
import com.css.bjdt_core.reportFill.entity.CellDataValue;
import com.css.bjdt_core.util.ReportConstant;

@Service
public class ReportTwoYearDataContrastService {

	@Autowired
	private Dao dao;

	public List<CellDataValue> getReportData(String dzzId, String annualId, String templateId) {
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		Sql strSql = Sqls.create("select CELL_VALUE,ABS_ROW_NUM,ABS_COL_NUM,CELL_TYPE from " + tableName
				+ " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId");
		strSql.params().set("dzzId", dzzId).set("templateId", templateId);
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<CellDataValue> cellDataValueList = new ArrayList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setAbsColNum(rs.getString("abs_col_num"));
					cellDataValue.setAbsRowNum(rs.getString("abs_row_num"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValue.setCellType(rs.getString("CELL_TYPE"));
					cellDataValueList.add(cellDataValue);
				}
				return cellDataValueList;
			}
		});
		dao.execute(strSql);
		return strSql.getList(CellDataValue.class);
	}

	public List<ReportExamineSumDzzId> getReportExamineSumByDzzId(String templateId, String absRowNum, String absColNum,
			String dzzId, String annualId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		Sql strSql = Sqls.create("select sum(1) as examineSum, t.be_audited_dzzid from " + tableName
				+ " t where t.template_id = @templateId and t.abs_row_num = @absRowNum and t.abs_col_num = @absColNum and t.be_audited_dzzid in "
				+ " (select t1.dzzid from t_m_zzinfo t1 where t1.sjdzzid = @dzzId )  group by t.be_audited_dzzid");
		strSql.params().set("templateId", templateId).set("absRowNum", absRowNum).set("absColNum", absColNum)
				.set("dzzId", dzzId);
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<ReportExamineSumDzzId> reportExamineSumDzzIdList = new ArrayList<ReportExamineSumDzzId>();
				while (rs.next()) {
					ReportExamineSumDzzId reportExamineSumDzzId = new ReportExamineSumDzzId();
					reportExamineSumDzzId.setDzzId(rs.getString("BE_AUDITED_DZZID"));
					reportExamineSumDzzId.setExamineSum(rs.getString("EXAMINESUM"));
					reportExamineSumDzzIdList.add(reportExamineSumDzzId);
				}
				return reportExamineSumDzzIdList;
			}
		});
		dao.execute(strSql);
		return strSql.getList(ReportExamineSumDzzId.class);
	}
}
